<?php

class ArticleTable
{
    const FILTER_TYPE     = 'type';
    const FILTER_CATEGORY = 'category';
    const FILTER_PRODUCT  = 'product';
    const FILTER_LANGUAGE = 'language';

    const SORT_PUBLISH_DATE = 'publish_date';

    // fixme: this is temp solution for Sales FAQ,
    // it's stored in table article_product, but this
    // table should be named something like 'article_application'
    const SALES_FAQ = 'sales';

    public static function GetItemById( $itemId )
    {
        $item = new ArticleInfo();

        $itemId = Util::Quote( $itemId );
        $sql = "SELECT * FROM article WHERE article_id = '$itemId';";
        $res = Core::DBConn()->Query( $sql )->GetAllAssoc();
        if ( !empty( $res[0] ) )
        {
            return self::CreateArticleInfo($res[0]);
        }

        return null;
    }

    public static function GetItemBySlug( $slug, $type = ArticleType::PRESS_RELEASE )
    {
        $item = null;

        $sql = 'SELECT * FROM `article` WHERE type = @type AND slug = @slug';
        $data = Core::DBConn()->Query($sql, Util::QuoteArray(array(
            '@type' => $type,
            '@slug'  => $slug
        )))->GetAssoc();

        if ( !empty($data) )
        {
            $item = self::CreateArticleInfo($data);
        }

        return $item;
    }

    public static function GetItemsByCategoryId( $categoryId )
    {
        $items = array();

        $categoryId = Util::Quote( $categoryId );
        $sql = "SELECT * FROM `article` WHERE `article_category_id`='$categoryId';";
        $res = Core::DBConn()->Query( $sql )->GetAllAssoc();
        if ( !empty( $res ) )
        {
            for ($i = 0; i < count($res); $i++)
            {
                $items[] = self::CreateArticleInfo($res[$i]);
            }
        }

        return $items;
    }

    public static function GetCountByCategoryId( $categoryId )
    {
        $sql = 'SELECT COUNT(*) as cnt FROM article WHERE article_category_id = %s';
        $data = Core::DBConn()->Query( $sql, Util::Quote($categoryId) )->GetAssoc();

        return !empty($data['cnt']) ? $data['cnt'] : 0;
    }

    public static function GetItemsByType( $type, $language = SiteLanguage::EN, $published = null, $sort = array() )
    {
        $type = Util::Quote($type);
        $language = Util::QuoteString($language);
        $where = "WHERE type = $type AND language = $language";
        if ( !is_null($published) )
        {
            $where .= " AND is_published = " . ($published ? '1' : '0');
        }

        if (!empty($sort))
        {
            $where .= ' ORDER BY';
            foreach ($sort as $column => $order)
            {
                $where .= ' ' . $column . ' ' . $order;
            }
        }

        $items = self::GetAllItems($where);
        return $items;
    }

    public static function GetItemsByProduct( $product, $type, $published = null, $language = null, $sort = array() )
    {
        $items = array();
        $sql = 'SELECT * FROM article a
                INNER JOIN article_product p ON a.article_id = p.article_id
                WHERE a.type = @type AND p.product = @product';

        if ( !is_null($published) )
        {
            $sql .= ' AND a.is_published = ' . ($published ? '1' : '0');
        }
        
        if ( !empty($language) )
        {
            $sql .= ' AND a.language = @language';
        }

        if (!empty($sort))
        {
            $sql .= ' ORDER BY';
            foreach ($sort as $column => $order)
            {
                $sql .= ' ' . $column . ' ' . $order;
            }
        }
        
        $res = Core::DBConn()->Query( $sql, Util::QuoteArray(array(
                '@type'     => $type,
                '@product'  => $product,
                '@language' => $language
            )) )->GetAllAssoc();

        if ( !empty($res) )
        {
            foreach ($res as $data)
            {
                $items[] = self::CreateArticleInfo($data);
            }
        }

        return $items;
    }

    public static function GetAllItems( $rangingRequest = "", $orderBy = "" )
    {
        $items = array();

        $sql = "SELECT * FROM `article`";
        if ( !empty( $rangingRequest ) )
        {
            $sql = "$sql $rangingRequest";
        }

        if ( !empty( $orderBy ) )
        {
            $sql = "$sql $orderBy";
        }

        $sql = $sql. ";";
        $res = Core::DBConn()->Query( $sql )->GetAllAssoc();
        if ( !empty( $res ) )
        {
            for ($i = 0; $i < count($res); $i++)
            {
                $items[] = self::CreateArticleInfo($res[$i]);
            }
        }

        return $items;
    }

    public static function GetItems( $searchText, $products = null )
    {

        $searchText = Util::Quote( $searchText );
        $searchText = trim("$searchText");

        $sql = 'SELECT * FROM article';

        $where = array();
        if ( !empty($searchText) )
        {
            $where[] = " ( (POSITION(#searchText# IN title) <> 0)
                         OR (POSITION(#searchText# IN content) <> 0) )";
        }

        $products = (array)$products;
        if (!empty($products))
        {
            $where[] = " article_id IN (SELECT article_id FROM article_product
                      WHERE article_product.article_id = article.article_id
                        AND article_product.product IN (" . implode(', ', Util::QuoteStringArray($products)) . ")
                          OR article_product.product = '')";
        }

        if (!empty($where))
        {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= " ORDER BY 'title'";
        $res = Core::DBConn()->Query(
            $sql, array('#searchText#' => Util::Quote($searchText))
        );
        $res = $res->GetAllAssoc();

        $items = array();
        if ( !empty( $res ) )
        {
            for ($i = 0; $i < count($res); $i++)
            {
                $items[] = self::CreateArticleInfo($res[$i]);
            }
        }

        return $items;
    }

    public static function GetRangedItems( $products, $type = ArticleType::PRESS_RELEASE, $published = null, $lang = 'en' )
    {
        $type = Util::Quote($type);
        $lang = Util::QuoteString($lang);
        $rangingRequest = " WHERE type = $type AND language = $lang";

        if ( !is_null($published) )
        {
            $rangingRequest .= " AND is_published = " . ($published ? '1' : '0');
        }

        $products = (array)$products;
        if (!empty($products))
        {
            $rangingRequest .= " AND article_id IN (SELECT article_id FROM article_product p
                  WHERE p.article_id = article.article_id
                    AND (p.product IN (" . implode(', ', Util::QuoteStringArray($products)) . ")
                        OR p.product = ''))";
        }

        return self::GetAllItems( $rangingRequest, "ORDER BY create_date DESC" );
    }

    /**
     *
     * @param array $filters
     * @param int $pageNumber
     * @param int $pageSize
     * @return Pager
     */
    public static function FindItems($filters = array(), $pageNumber = 1, $pageSize = 10)
    {
        $pager = new Pager($pageNumber, $pageSize);

        $query = 'SELECT a.* FROM article a ';
        $totalQuery = 'SELECT COUNT(*) AS total FROM article a ';
        $join = '';
        $where = array();

        $filters = (array)$filters;
        if ( !empty( $filters[self::FILTER_TYPE] ) )
        {
            $where[] = 'a.type = ' . Util::Quote( $filters[self::FILTER_TYPE] );
        }
        if ( !empty( $filters[self::FILTER_CATEGORY] ) )
        {
            $where[] = 'a.article_category_id = ' . Util::Quote( $filters[self::FILTER_CATEGORY] );
        }
        if ( !empty( $filters[self::FILTER_PRODUCT] ) )
        {
            $join = 'INNER JOIN article_product p ON a.article_id = p.article_id';
            $where[] = 'p.product = ' . Util::QuoteString( $filters[self::FILTER_PRODUCT] );
        }
        if ( !empty( $filters[self::FILTER_LANGUAGE] ) )
        {
            $where[] = 'a.language = ' . Util::QuoteString( $filters[self::FILTER_LANGUAGE] );
        }

        $whereClause = !empty($where) ? ' WHERE ' . implode(' AND ', $where) : '';

        $totalQuery .= $join . $whereClause;
        $totalRes = Core::DBConn()->Query($totalQuery)->GetAssoc();
        $total = isset($totalRes['total']) ? $totalRes['total'] : 0;

        $results = array();
        if ( $total > 0 )
        {
            $offset = ($pageNumber - 1) * $pageSize;
            $query .= $join . $whereClause . " ORDER BY a.create_date DESC LIMIT $offset, $pageSize";
            $res = Core::DBConn()->Query($query)->GetAllAssoc();
            foreach ($res as $data)
            {
                $results[] = self::CreateArticleInfo($data);
            }
        }

        $pager->SetResults($results, $total);

        return $pager;
    }

    public static function FindItemByQuestion( $searchText )
    {
        $searchText = trim( $searchText );
        $items = self::GetItems( $searchText );
        $res = null;
        foreach ( $items as $item )
        {
            if ( trim( $item->getTitle() ) === $searchText )
            {
                return $item;
            }
        }

        return $res;
    }

    public static function FindItemByTag($keyword, $type = ArticleType::PRESS_RELEASE, $product = null, $lang = 'en')
    {
        $keyword = Util::QuoteString( trim( $keyword ) );
        $lang = Util::QuoteString($lang);

        $sql = "SELECT * FROM article a ";
        $where = " WHERE a.type = $type AND a.language = $lang
                    AND POSITION($keyword IN tags) <> 0";

        if ( !empty($product) )
        {
            $product = Util::QuoteString( $product );

            $sql .= "INNER JOIN article_product p ON a.article_id = p.article_id";
            $where .= " AND p.product = $product";
        }
        $sql .= $where . " LIMIT 1";

        $res = Core::DBConn()->Query( $sql );

        $res = $res->GetAllAssoc();
        $item = null;
        if ( !empty($res[0]) )
        {
            $item = self::CreateArticleInfo($res[0]);
        }
        return $item;
    }

    public static function GetItemsDividedByCategories( $products = null, $type = ArticleType::PRESS_RELEASE, $published = null, $lang = 'en' )
    {
        $categories = ArticleCategoryTable::GetCategories($lang);
        $basicItems = self::GetRangedItems($products, $type, $published, $lang);

        $resItems   = array();
        foreach ( $categories as $category )
        {
            $categoryItems = array();
            foreach ( $basicItems as $item )
            {
                if ( $item->GetCategoryId() == $category->GetCategoryId() )
                {
                    $categoryItems[] = $item;
                }
                else if ( ( $item->GetCategoryId() == null ) && ( $category->GetCategoryId() == 1 ) )
                {
                    $categoryItems[] = $item;
                }
            }
            $resItems[$category->GetName()] = $categoryItems;
        }
        return $resItems;
    }

    /**
     * Returns array of most popular viewed articles.
     *
     * @param string $products
     * @param int $type
     * @param int $limit
     * @param string $lang
     * @return array
     */
    public static function GetMostPopularViewed( $products = null, $type = ArticleType::PRESS_RELEASE, $limit = 10, $lang = 'en' )
    {
        $type = Util::Quote($type);
        $lang = Util::QuoteString($lang);
        $sql = "SELECT * FROM article WHERE type = $type AND is_published = 1 AND language = $lang";

        $products = (array)$products;
        if (!empty($products))
        {
            $sql .= " AND article_id IN (SELECT article_id FROM article_product p
                                         WHERE p.article_id = article.article_id
                                         AND p.product IN (" . implode(', ', Util::QuoteStringArray($products)) . "))";
        }

        $sql .= " ORDER BY monthly_unique_views LIMIT " . Util::Quote( $limit ) . ";";

        $res = Core::DBConn()->Query( $sql )->GetAllAssoc();

        $items = array();
        if ( !empty( $res ) )
        {
            for ($i = 0; $i < count($res); $i++)
            {
                $items[] = self::CreateArticleInfo( $res[$i] );
            }
        }

        return $items;
    }

    public static function SaveArticle(ArticleInfo $article, $products)
    {
        if ($article->GetArticleId() > 0)
        {
            self::Update($article);
        }
        else
        {
            self::Insert($article);
        }

        self::UpdateArticleProducts($article->GetArticleId(), $products);

        return $article->GetArticleId();
    }

    public static function Insert(ArticleInfo $article)
    {
        $sql = 'INSERT INTO article
                    (type, language, title, content, excerpt, article_category_id,
                     meta_title, slug, is_published, tags, monthly_unique_views,
                     create_date, publish_date)
                VALUE
                    (@type, @language, @title, @content, @excerpt, @category_id,
                     @meta_title, @slug, @is_published, @tags, 0, @create_date,
                     @publish_date)';

        $article->SetCreateDate(time());

        $params = array
        (
            '@type'                 => $article->GetType(),
            '@language'             => $article->GetLanguage(),
            '@title'                => $article->GetTitle(),
            '@content'              => $article->GetContent(),
            '@excerpt'              => $article->GetExcerpt(),
            '@category_id'          => $article->GetCategoryId(),
            '@meta_title'           => $article->GetMetaTitle(),
            '@slug'                 => $article->GetSlug(),
            '@is_published'         => $article->GetIsPublished(),
            '@tags'                 => $article->GetTags(),
            '@create_date'          => date(DateUtils::ISO_DATETIME_PATTERN, $article->GetCreateDate()),
            '@publish_date'         => date(DateUtils::ISO_DATETIME_PATTERN, $article->GetPublishDate())
        );

        Core::DBConn()->Query( $sql, Util::QuoteArray($params) );

        $id = Core::DBConn()->GetLastInsertId();

        $article->SetArticleId($id);

        self::SaveSlug($article);

        return $id;
    }

    public static function Update(ArticleInfo $article)
    {
        $sql = 'UPDATE article
                SET
                  type = @type,
                  language = @language,
                  title = @title,
                  content = @content,
                  excerpt = @excerpt,
                  article_category_id = @category_id,
                  meta_title = @meta_title,
                  slug = @slug,
                  is_published = @is_published,
                  tags = @tags,
                  monthly_unique_views = @monthly_unique_views,
                  publish_date = @publish_date,
                  update_date = NOW()
                WHERE article_id = @article_id';

        $params = array
        (
            '@type'                 => $article->GetType(),
            '@language'             => $article->GetLanguage(),
            '@title'                => $article->GetTitle(),
            '@content'              => $article->GetContent(),
            '@excerpt'              => $article->GetExcerpt(),
            '@category_id'          => $article->GetCategoryId(),
            '@meta_title'           => $article->GetMetaTitle(),
            '@slug'                 => $article->GetSlug(),
            '@is_published'         => $article->GetIsPublished(),
            '@tags'                 => $article->GetTags(),
            '@monthly_unique_views' => $article->GetMonthlyUniqueViews(),
            '@publish_date'         => date(DateUtils::ISO_DATETIME_PATTERN, $article->GetPublishDate()),
            '@article_id'           => $article->GetArticleId()
        );

        Core::DBConn()->Query( $sql, Util::QuoteArray($params) );

        return $article->GetArticleId();
    }

    public static function UpdateArticleProducts($articleId, $products = array())
    {
        $sqlDelete = 'DELETE FROM article_product WHERE article_id = %s';
        Core::DBConn()->Query( $sqlDelete, Util::Quote($articleId) );

        if ( empty($products) )
        {
            return;
        }

        $sqlAdd = 'INSERT INTO article_product (article_id, product)
                    VALUES ';
        foreach ($products as $index => $product)
        {
            $sqlAdd .= "('" . $articleId . "', '" . $product . "')";
            if ($index + 1 == count($products))
            {
                $sqlAdd .= ";";
            }
            else
            {
                $sqlAdd .= ", ";
            }
        }
        Core::DBConn()->Query( $sqlAdd );
    }

    public static function Delete( $articleId )
    {
        Core::DBConn()->Query( 'DELETE FROM article WHERE article_id = @article_id',
            Util::QuoteArray( array('@article_id' => $articleId) )
        );
    }

    public static function GetProducts( $articleId )
    {
        $products = array();

        $sql = "SELECT * FROM `article_product` WHERE article_id = %s";
        $res = Core::DBConn()->Query( $sql, Util::Quote($articleId) )->GetAllAssoc();
        if ( !empty( $res ) )
        {
            for ($i = 0; $i < count($res); $i++)
            {
                $products[] = $res[$i]["product"];
            }
        }
        return $products;
    }

    private static function SaveSlug(ArticleInfo $article)
    {
        $slug = $article->GenerateSlug();
        
        // if duplicate slug then append id
        if ( self::GetItemBySlug($slug, $article->GetType()) )
        {
            $slug = $article->GetArticleId() . '-' . $slug;
        }        

        Core::DBConn()->Query(
            "UPDATE article SET slug = @slug WHERE article_id = @article_id",
            Util::QuoteArray(array(
                '@slug' => $slug,
                '@article_id' => $article->GetArticleId()
            ))
        );
    }


    private static function CreateArticleInfo( $data )
    {
        $item = new ArticleInfo();

        $item->SetArticleId($data["article_id"]);
        $item->SetLanguage($data["language"]);
        $item->SetType($data["type"]);
        $item->SetTitle($data["title"]);
        $item->SetContent($data["content"]);
        $item->SetExcerpt($data["excerpt"]);
        $item->SetCategoryId($data["article_category_id"]);
        $item->SetMetaTitle($data["meta_title"]);
        $item->SetSlug($data["slug"]);
        $item->SetIsPublished($data["is_published"]);
        $item->SetTags($data['tags']);
        $item->SetMonthlyUniqueViews($data["monthly_unique_views"]);
        $item->SetCreateDate($data["create_date"]);
        $item->SetUpdateDate($data["update_date"]);
        $item->SetPublishDate($data["publish_date"]);

        return $item;
    }
}

?>